Creating conditional running totals

There may be times when you have a list of ungrouped values, and you only want to subtotal some of the values in the list. For example:

To accomplish this, create two running totals, one to keep a running total of the U.S. records, and one to keep a running total of the Canadian records.

To create a conditional running total
  1. To get started, create a report using the sample data, Xtreme.mdb. Place the following fields from left to right in the Details section:

    {customer.CUSTOMER NAME}

    {customer.COUNTRY}

    {customer.LAST YEAR'S SALES}

  2. On the Report menu, click Sort Records.

    Tip:     Another way to do this is to click the Sort button on the Standard toolbar.

  3. Sort the records based on the {customer.CUSTOMER NAME} field.
  4. On the Insert menu, click Field Object.

    The Field Explorer dialog box appears.

  5. Select Running Total Fields and click New.

    The Create Running Total Field dialog box appears.

  6. Enter the name "USTotal" in the Running Total Name box.
  7. Highlight {Customer.LAST YEAR'S SALES} in the Available Tables and Fields box, and use the first arrow button to move it over to the Field to summarize box.
  8. Select sum from the Type of summary list.
  9. In the Evaluate section of the dialog box, click Use a formula and then click the Formula button.

    The Running Total Condition Formula Editor appears.

  10. Enter the following formula in the formula box:
    {Customer.Country} = "USA"

    This tells the program to evaluate the running total each time it comes to a record where {Customer.COUNTRY} is equal to "USA." The running total will ignore all other records (such as records for Canada).

  11. When the formula has the correct syntax, click Save and Close.

    You return to the Create Running Total Field dialog box.

  12. In the Reset section of the dialog box, click Never.
  13. Click OK to save the running total field.

    The program returns you to the Field Explorer dialog box.

  14. Place the running total field in the Details section of your report.
  15. Now create the "CanadaTotal" running total field using the process outlined in steps 3-13. The only difference is that this time you will set the Evaluate formula to:

    {Customer.Country} = "Canada"

  16. When you are finished, place the #CanadaTotal field in the Details section of your report, just to the right of the {Customer.LAST YEAR'S SALES} field.
  17. If you only want to see a grand total of the Canadian and American sales, place the two running total fields you created in the Report Footer section of your report.


Seagate Software IMG Holdings, Inc.
http://www.seagatesoftware.com
Support services:
http://support.seagatesoftware.com